package usi.ch.onto.yago;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class TaxonomyBuilder {
	private final String CONNECTION_STRING = "jdbc:mysql://localhost/yago";
	private final int BATCH_MAX = 30000;
	private final String USER = "root";
	private final String PASS = "admin";
	private final String RESULTS_TABLE_SUFIX = "results";

	private String subClassTableName;
	private String treeTableName;
	private boolean insertSuperClasses;
	private OriginTable origintable;
	private Connection conn;
	private PreparedStatement insertTaxonomystmt;
	private PreparedStatement selectStmt;
	private PreparedStatement selectByIdStmt;
	private PreparedStatement deleteNodes;
	private CallableStatement procInsertSuperClass;
	private CallableStatement procInsertAllSuperClass;

	public enum OriginTable {
		subclassResults, tabTypes, taxonomy
	}

	/**
	 * 
	 * @param subClassTableName
	 * @param origintable
	 *            must be a results table type
	 * @throws Exception
	 */
	public TaxonomyBuilder(String subClassTableName, OriginTable origintable,
			String treeTableName, boolean insertSuperClasses) throws Exception {
		conn = DriverManager.getConnection(CONNECTION_STRING, USER, PASS);
		if (conn.isClosed())
			throw new Exception("Error connection to db ");

		this.subClassTableName = subClassTableName;
		this.origintable = origintable;
		this.insertSuperClasses = insertSuperClasses;
		this.treeTableName = treeTableName;

		this.insertTaxonomystmt = getTreeTableInsertPpstmt();
		this.selectStmt = getSubClassTableSelectSuperClassPpstmt();
		this.selectByIdStmt = getSubClassTableSelectSuperClassIdPpstmt();
		this.procInsertSuperClass = conn
				.prepareCall("call insertSuperClassesOf(?)");
		this.procInsertAllSuperClass = conn.prepareCall("call InserAllSuperClasses()");
		
		this.deleteNodes = conn.prepareStatement("delete from taxonomy where count <= ?");
	}

	public void createTree() throws SQLException {
		if (origintable == OriginTable.subclassResults) {
			processSubClassResults();
		} else
			processTabTypes();

	}

	public void insertSuperClassesOfExistingClassesInTree() throws SQLException{
		procInsertAllSuperClass.execute();
	}
	
	public void deleteAllEntriesWithLessThanOrEqual(int count) throws SQLException{
		deleteNodes.setInt(1, count);
		deleteNodes.executeUpdate();
	}

	private PreparedStatement getTreeTableInsertPpstmt() throws SQLException {
		PreparedStatement ppstmt = conn
				.prepareStatement("insert ignore into "
						+ treeTableName
						+ " (tabid, isFromClassResults, isFromTypeResults, isSuperClass) values (?, ?, ?, ?)");

		return ppstmt;
	}

	private PreparedStatement getSubClassTableSelectSuperClassIdPpstmt()
			throws SQLException {
		return conn.prepareStatement("select arg2 from " + subClassTableName
				+ " where id = ?");

	}

	private PreparedStatement getSubClassTableSelectSuperClassPpstmt()
			throws SQLException {
		PreparedStatement ppstmt = conn
				.prepareStatement("select id, arg2 from " + subClassTableName
						+ " where arg1 = ?");

		return ppstmt;
	}

	/**
	 * 
	 * @param stmt
	 * @param tabid
	 * @param isFromClassResults
	 * @param isFromTypeResults
	 * @param isSuperClass
	 * @throws SQLException
	 */
	private void insertIntoTaxonomy(PreparedStatement stmt, long tabid,
			boolean isFromClassResults, boolean isFromTypeResults,
			boolean isSuperClass) throws SQLException {
		// insert the type in taxonomy
		stmt.setLong(1, tabid);
		stmt.setBoolean(2, isFromClassResults); // isFromClassResults
		stmt.setBoolean(3, isFromTypeResults); // isFromTypeResults
		stmt.setBoolean(4, isSuperClass); // isSuperClass

		stmt.executeUpdate();
	}

	private void processSubClassResults() throws SQLException {
		ResultSet resultSet;
		List<Long> listSubClassIds = getTableTabIds(OriginTable.subclassResults);
		String superclassname;
		for (Long subclassid : listSubClassIds) {
			// find the type in subclass
			insertIntoTaxonomy(insertTaxonomystmt, subclassid, true, false,
					false);

			if (insertSuperClasses) {
				selectByIdStmt.setLong(1, subclassid);
				resultSet = selectByIdStmt.executeQuery();
				if (resultSet.next()) {
					superclassname = resultSet.getString(1);
					insertAllSuperClassesOf(superclassname);
				}
			}
		}
	}

	private void processTabTypes() throws SQLException {
		ResultSet resultSet;
		List<String> types = getTypesList();
		String superclassname;
		Long subClassid;
		for (String type : types) {
			// find the type in subclass
			selectStmt.setString(1, type);
			resultSet = selectStmt.executeQuery();
			if (resultSet.next()) {
				subClassid = resultSet.getLong(1);
				superclassname = resultSet.getString(2);
				insertIntoTaxonomy(insertTaxonomystmt, subClassid, false, true,
						false);
			} else {
				System.out.println("type not found in subclass table type:"
						+ type);
				continue;
			}

			// insert superclasses with procedure
			if (insertSuperClasses)
				insertAllSuperClassesOf(superclassname);

		}
	}

	private void insertAllSuperClassesOf(String subclass) throws SQLException {
		// call procedure insertSuperClassesOf
		procInsertSuperClass.setString(1, subclass);
		procInsertSuperClass.execute();
	}

	private List<Long> getTableTabIds(OriginTable whichTable ) throws SQLException {
		String table;
		if (whichTable == OriginTable.subclassResults)
			table = "subclassResults";
		else if (whichTable == OriginTable.taxonomy)
			table = "taxonomy";
		else 
			table = "subclassResults";
		
		PreparedStatement ppstmt = conn
				.prepareStatement("select tabid from " + table);

		List<Long> list = new ArrayList<Long>();
		ResultSet set = ppstmt.executeQuery();
		while (set.next()) {
			list.add(set.getLong(1));
		}
		return list;
	}

	private List<String> getTypesList() throws SQLException {
		// load all types into a list
		PreparedStatement ppstmt = conn
				.prepareStatement("select distinct id, name from tabTypes");

		List<String> typesList = new ArrayList<String>();
		ResultSet set = ppstmt.executeQuery();
		while (set.next()) {
			typesList.add(set.getString(2));
		}
		return typesList;
	}
}
